/*
 * BSM6.0
 *
 * Copyright 2012-2013 (C) SINODATA CO., LTD. All Rights Reserved.
 */
package com.sinodata.bsm.center.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import com.sinodata.bsm.center.bean.Condition;
import com.sinodata.bsm.center.bean.Page;
import com.sinodata.bsm.center.dao.BaseDao;
import com.sinodata.bsm.center.dao.PropertyValueDao;
import com.sinodata.bsm.common.vo.PropertyValue;

/**
 * <p>
 * Description:
 * </p>
 * 
 * @author tangli
 * @version 1.0
 * 
 * <p>
 * History:
 * 
 * Date Author Version Description
 * ---------------------------------------------------------------------------------
 * 2012-6-13 上午10:46:43 tangli 1.0 To create
 * </p>
 * 
 * @since
 * @see
 */
@Repository
public class PropertyValueDaoImpl extends BaseDao implements PropertyValueDao {

    private static Logger logger = Logger.getLogger(PropertyValueDaoImpl.class);

    private static String INSERT_SQL = null;

    private static String PROPERTYVALUE = "property_value";

    private static String PROPERTYOBJECTVALUE = "property_object_value";

    // 时汇聚表
    private static String HOURREDUCETABLE = "reduce_hour_property_value";

    // 日汇聚表
    private static String DAYREDUCETABLE = "reduce_day_property_value";

    //日汇聚 方差
    private static String DAYREDUCEVALTABLE = "REDUCE_PROPERTY_VAR_DAY";
    private static String MONREDUCEVALTABLE = "REDUCE_PROPERTY_VAR_MON";

    private static String INSERT_OBJECT_SQL = "INSERT INTO PROPERTY_OBJECT_VALUE (RES_ID,PROP_ID,TIME,DATA1,DATA2,DATA3,DATA4,DATA5,DATA6,DATA7,DATA8,DATA9,DATA10,DATA11,DATA12,DATA13,DATA14,DATA15,DATA16,DATA17,DATA18,DATA19,DATA20,FILE_CONTENT) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    static {
        INSERT_SQL = "insert into property_value (resid, time";
        for (int i = 1; i <= 200; i++) {
            INSERT_SQL += ", prop" + i;
        }
        INSERT_SQL += ") values(?,?";
        for (int i = 1; i <= 200; i++) {
            INSERT_SQL += ", ?";
        }
        INSERT_SQL += ")";
    }

    /**
     * 
     */
    public PropertyValueDaoImpl() {
    }

    @Override
    public void insert(final List<Map<String, Object>> values) {
        this.getSession().doWork(new Work() {
            @Override
            public void execute(Connection conn) throws SQLException {
                try {
                    PreparedStatement stmt = null;
                    stmt = conn.prepareStatement(INSERT_SQL);
                    for (Map<String, Object> map : values) {
                        Long resId = (Long) map.get("RESID");
                        Date time = (Date) map.get("TIME");
                        stmt.setLong(1, resId);
                        stmt.setTimestamp(2, new Timestamp(time.getTime()));
                        int index = 3;
                        for (int i = 1; i <= 200; i++) {
                            String key = "PROP" + i;
                            String value = (String) map.get(key);
                            if (value == null || value.length() == 0) {
                                stmt.setNull(index, java.sql.Types.VARCHAR);
                            } else {
                                stmt.setString(index, value);
                            }
                            index++;
                        }
                        stmt.addBatch();
                    }
                    stmt.executeBatch();
                    conn.commit();
                    stmt.close();
                } finally {
                    conn.close();
                }
            }
        });
    }

    @Override
    public void insertObjectValues(final List<Object[]> values) {
        this.getSession().doWork(new Work() {

            @Override
            public void execute(Connection conn) throws SQLException {
                try {
                    PreparedStatement stmt = null;
                    stmt = conn.prepareStatement(INSERT_OBJECT_SQL);
                    for (Object[] value : values) {
                        Long resId = (Long) value[0];
                        Long propId = (Long) value[1];
                        Date time = (Date) value[2];
                        stmt.setLong(1, resId);
                        stmt.setLong(2, propId);
                        stmt.setTimestamp(3, new Timestamp(time.getTime()));
                        for (int i = 1; i <= 20; i++) {
                            String val = (String) value[i + 2];
                            if (val == null || val.length() == 0) {
                                stmt.setNull(i + 3, java.sql.Types.VARCHAR);
                            } else {
                                stmt.setString(i + 3, val);
                            }
                        }
                        if (value[23] == null || ((String) value[23]).length() == 0) {
                            stmt.setNull(24, java.sql.Types.VARCHAR);
                        } else {
                            stmt.setString(24, (String) value[23]);
                        }
                        stmt.addBatch();
                    }
                    stmt.executeBatch();
                    conn.commit();
                    stmt.close();
                } finally {
                    conn.close();
                }
            }
        });
    }

    @SuppressWarnings({ "deprecation" })
    public List<PropertyValue> getReducePropByDate(Long resId, int[] storeOrder, String startDate, String endDate, String reduceType) {
        List<PropertyValue> propList = new ArrayList<PropertyValue>();
        StringBuffer sql = new StringBuffer("select resid,time");
        StringBuilder sb = new StringBuilder();
        for (int store : storeOrder) {
            sb.append(" ,avg" + store + ", cnt" + store + ", max" + store + ", min" + store);
        }
        sql.append(sb.toString());
        if ("1".equals(reduceType)) {
            sql.append(" from " + HOURREDUCETABLE + " p where 1=1 ");
        }
        if ("2".equals(reduceType)) {
            sql.append(" from " + DAYREDUCETABLE + " p where 1=1 ");
        }

        if (resId >= 0) {
            sql.append(" and p.resid =" + resId + " ");
        }
        for (int i = 0; i < storeOrder.length; i++) {
            if (storeOrder.length == 1) {
                sql.append(" and  p.avg" + storeOrder[i] + " is not null");
            } else {
                if (i == 0) {
                    sql.append(" and ( p.avg" + storeOrder[i] + " is not null");
                } else if (i == (storeOrder.length - 1)) {
                    sql.append(" or p.avg" + storeOrder[i] + " is not null )");
                } else {
                    sql.append(" or p.avg" + storeOrder[i] + " is not null");
                }
            }
        }
        if (startDate != null && startDate.length() > 0) {
            sql.append(" and p.time >= to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        if (endDate != null && endDate.length() > 0) {
            sql.append(" and p.time <= to_date('" + endDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        sql.append(" order by p.time asc ");

        logger.info(sql);
        // todo
        // System.out.println(sql);
        // Query query = getSession().createSQLQuery(sql.toString());

        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            pStmt = getSession().connection().prepareStatement(sql.toString());
            rs = pStmt.executeQuery();
            while (rs.next()) {
                PropertyValue pv = new PropertyValue();
                pv.setResId(resId);
                pv.setTime(rs.getTimestamp("time"));
                String[] values = new String[storeOrder.length * 4];
                for (int i = 0; i < storeOrder.length; i++) {
                    if (storeOrder[i] <= 0) {
                        values[i] = "";
                        continue;
                    }
                    values[4 * i] = rs.getString("avg" + storeOrder[i]);
                    values[4 * i + 1] = rs.getString("cnt" + storeOrder[i]);
                    values[4 * i + 2] = rs.getString("max" + storeOrder[i]);
                    values[4 * i + 3] = rs.getString("min" + storeOrder[i]);
                }
                pv.setValues(values);
                propList.add(pv);
            }

        } catch (Exception e) {
            logger.error(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e2) {
                }
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (Exception e2) {
                }
            }
        }
        return propList;
    }

    @SuppressWarnings({ "deprecation" })
    @Override
    public List<PropertyValue> getTrendPropByDate(Long resId, int[] storeOrder, String startDate, String endDate, String trendType, String reduceType) {
        List<PropertyValue> propList = new ArrayList<PropertyValue>();
        StringBuffer sql = new StringBuffer("select resid,time");
        StringBuilder sb = new StringBuilder();
        String type = "";
        if ("0".equals(trendType)) {
            type = "avg";
        } else if ("1".equals(trendType)) {
            type = "max";
        } else {
            type = "min";
        }

        for (int store : storeOrder) {
            sb.append(" ," + type + store);
        }

        sql.append(sb.toString());
        if ("1".equals(reduceType)) {
            sql.append(" from " + HOURREDUCETABLE + " p where 1=1 ");
        }
        if ("2".equals(reduceType)) {
            sql.append(" from " + DAYREDUCETABLE + " p where 1=1 ");
        }

        if (resId >= 0) {
            sql.append(" and p.resid =" + resId + " ");
        }
        for (int i = 0; i < storeOrder.length; i++) {
            if (storeOrder.length == 1) {
                sql.append(" and  p." + type + storeOrder[i] + " is not null");
            } else {
                if (i == 0) {
                    sql.append(" and ( p." + type + storeOrder[i] + " is not null");
                } else if (i == (storeOrder.length - 1)) {
                    sql.append(" or p." + type + storeOrder[i] + " is not null )");
                } else {
                    sql.append(" or p." + type + storeOrder[i] + " is not null");
                }
            }
        }

        if (startDate != null && startDate.length() > 0) {
            sql.append(" and p.time >= to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        if (endDate != null && endDate.length() > 0) {
            sql.append(" and p.time <= to_date('" + endDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        sql.append(" order by p.time asc ");
        // todo
        // System.out.println(sql);
        // logger.info(sql);
        // Query query = getSession().createSQLQuery(sql.toString());

        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            pStmt = getSession().connection().prepareStatement(sql.toString());
            rs = pStmt.executeQuery();

            while (rs.next()) {
                PropertyValue pv = new PropertyValue();
                pv.setResId(resId);
                pv.setTime(rs.getTimestamp("time"));
                String[] values = new String[storeOrder.length];
                for (int i = 0; i < storeOrder.length; i++) {
                    if (storeOrder[i] <= 0) {
                        values[i] = "";
                        continue;
                    }
                    values[i] = rs.getString(type + storeOrder[i]);
                }
                pv.setValues(values);
                propList.add(pv);
            }

        } catch (Exception e) {
            logger.error(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e2) {
                }
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (Exception e2) {
                }
            }
        }
        return propList;
    }

    @SuppressWarnings({ "deprecation" })
    @Override
    public Map<String, String> queryCountPropValuesBySpace(Long resId, int[] storeOrder, String startDate, String endDate, String reduceType, List<String> spaceTimeList) {
        StringBuffer sql = new StringBuffer("select ");
        List<String> columnNameList = new LinkedList<String>();
        if (spaceTimeList != null && spaceTimeList.size() > 0) {

            for (String spaceTime : spaceTimeList) {
                StringBuffer spaceTimeBuffer = new StringBuffer();
                spaceTimeBuffer.append("sum(case when  p.prop51 between ");
                String[] apaceTimeArray = spaceTime.split("#");
                if (apaceTimeArray[0] != null) {
                    spaceTimeBuffer.append(apaceTimeArray[0]);
                    spaceTimeBuffer.append(" and ");
                    spaceTimeBuffer.append(apaceTimeArray[1]);
                    String columnName = apaceTimeArray[0] + "-" + apaceTimeArray[1];
                    columnNameList.add(columnName);
                    spaceTimeBuffer.append(" then 1 else 0 end ) \"" + columnName + "\",");
                    sql.append(spaceTimeBuffer);
                }
            }
        }
        sql.append(" sum(case when  p.prop51>1000  then 1 else 0 end) as \"1000\",");
        sql.append("  count(*) \"count\"");
        StringBuilder sb = new StringBuilder();
        // for (int store : storeOrder) {
        // sb.append(", p.prop" + store);
        // }
        sql.append(sb.toString());
        sql.append(" from " + PROPERTYVALUE + " p where 1=1 ");
        if (resId >= 0) {
            sql.append(" and p.resid =" + resId + " ");
        }
        for (int i = 0; i < storeOrder.length; i++) {
            if (storeOrder[i] > 0) {
                if (storeOrder.length == 1) {
                    sql.append(" and  p.PROP" + storeOrder[i] + " is not null");
                } else {
                    if (i == 0) {
                        sql.append(" and ( p.PROP" + storeOrder[i] + " is not null");
                    } else if (i == (storeOrder.length - 1)) {
                        sql.append(" or p.PROP" + storeOrder[i] + " is not null )");
                    } else {
                        sql.append(" or p.PROP" + storeOrder[i] + " is not null");
                    }
                }
            }
        }
        if (startDate != null && startDate.length() > 0) {
            sql.append(" and p.time >= to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        if (endDate != null && endDate.length() > 0) {
            sql.append(" and p.time <= to_date('" + endDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        sql.append(" order by p.time asc ");

        logger.info(sql);
        // Query query = getSession().createSQLQuery(sql.toString());

        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Map<String, String> dataMap = new LinkedHashMap<String, String>();
        try {
            pStmt = getSession().connection().prepareStatement(sql.toString());
            rs = pStmt.executeQuery();
            while (rs.next()) {
                if (rs.getString("count") == null || "0".equals(rs.getString("count"))) {
                    continue;
                }
                for (String columnName : columnNameList) {
                    dataMap.put(columnName + "毫秒", rs.getString(columnName));
                }
                if (rs.getString("1000") != null) {
                    dataMap.put("超过1000毫秒", rs.getString("1000"));
                }
                dataMap.put("count", rs.getString("count"));
            }
        } catch (Exception e) {
            logger.error(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e2) {
                }
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (Exception e2) {
                }
            }
        }
        return dataMap;
    }

    @SuppressWarnings({ "deprecation" })
    @Override
    public List<PropertyValue> getPropValuesByDate(Long resId, int[] storeOrder, String startDate, String endDate) {
        List<PropertyValue> propList = new ArrayList<PropertyValue>();
        // StringBuffer sql = new StringBuffer("select rownum , p.resid
        // ,to_char(p.time,'yyyy-mm-dd hh24:mi:ss')");
        StringBuffer sql = new StringBuffer("select resid,time ");
        StringBuilder sb = new StringBuilder();
        for (int store : storeOrder) {
            sb.append(", p.prop" + store);
        }
        sql.append(sb.toString());
        sql.append(" from " + PROPERTYVALUE + " p where 1=1 ");
        if (resId >= 0) {
            sql.append(" and p.resid =" + resId + " ");
        }
        for (int i = 0; i < storeOrder.length; i++) {
            if (storeOrder[i] > 0) {
                if (storeOrder.length == 1) {
                    sql.append(" and  p.PROP" + storeOrder[i] + " is not null");
                } else {
                    if (i == 0) {
                        sql.append(" and ( p.PROP" + storeOrder[i] + " is not null");
                    } else if (i == (storeOrder.length - 1)) {
                        sql.append(" or p.PROP" + storeOrder[i] + " is not null )");
                    } else {
                        sql.append(" or p.PROP" + storeOrder[i] + " is not null");
                    }
                }
            }
        }
        if (startDate != null && startDate.length() > 0) {
            sql.append(" and p.time >= to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        if (endDate != null && endDate.length() > 0) {
            sql.append(" and p.time <= to_date('" + endDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        sql.append(" order by p.time asc ");

        logger.info(sql);
        // Query query = getSession().createSQLQuery(sql.toString());

        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            pStmt = getSession().connection().prepareStatement(sql.toString());
            rs = pStmt.executeQuery();
            while (rs.next()) {
                PropertyValue pv = new PropertyValue();
                pv.setResId(resId);
                pv.setTime(rs.getTimestamp("time"));
                String[] values = new String[storeOrder.length];
                // boolean append =false;
                for (int i = 0; i < storeOrder.length; i++) {
                    if (storeOrder[i] <= 0) {
                        values[i] = "";
                        continue;
                    }
                    values[i] = rs.getString("PROP" + storeOrder[i]);
                    if (values[i] == null) {
                        values[i] = "";
                    }
                }
                pv.setValues(values);
                propList.add(pv);
            }
        } catch (Exception e) {
            logger.error(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e2) {
                }
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (Exception e2) {
                }
            }
        }

        return propList;
    }

    @SuppressWarnings({ "deprecation", "rawtypes", "unchecked" })
    @Override
    public Page getPropValuesByPage(Long resId, Page page, int[] storeOrder, String startDate, String endDate) {
        // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<PropertyValue> propList = new ArrayList<PropertyValue>();
        StringBuffer sql = new StringBuffer("select resid,time ");
        StringBuilder sb = new StringBuilder();
        for (int store : storeOrder) {
            sb.append(", p.prop" + store);
        }
        sql.append(sb.toString());
        sql.append(" from " + PROPERTYVALUE + " p where 1=1 ");
        if (resId >= 0) {
            sql.append(" and p.resid =" + resId + " ");
        }
        for (int i = 0; i < storeOrder.length; i++) {
            if (storeOrder[i] > 0) {
                if (storeOrder.length == 1) {
                    sql.append(" and  p.PROP" + storeOrder[i] + " is not null");
                } else {
                    if (i == 0) {
                        sql.append(" and ( p.PROP" + storeOrder[i] + " is not null");
                    } else if (i == (storeOrder.length - 1)) {
                        sql.append(" or p.PROP" + storeOrder[i] + " is not null )");
                    } else {
                        sql.append(" or p.PROP" + storeOrder[i] + " is not null");
                    }
                }
            }
        }
        if (startDate != null && startDate.length() > 0) {
            sql.append(" and p.time >= to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        if (endDate != null && endDate.length() > 0) {
            sql.append(" and p.time <= to_date('" + endDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        sql.append(" order by p.time desc ");
        String querySql = sql.toString();
        if (page != null) {
            querySql = pageOrderSql(sql.toString(), page.getPageNo(), page.getPageSize());
        }

        // td test
        // System.out.println(querySql);
        // logger.info(querySql);

        // 总条数
        page.setTotal(getTotalBySql(sql.toString()));

        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            pStmt = getSession().connection().prepareStatement(querySql);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                PropertyValue pv = new PropertyValue();
                pv.setResId(resId);
                pv.setTime(rs.getTimestamp("time"));
                String[] values = new String[storeOrder.length];
                // boolean append =false;
                for (int i = 0; i < storeOrder.length; i++) {
                    if (storeOrder[i] <= 0) {
                        values[i] = "";
                        continue;
                    }
                    values[i] = rs.getString("PROP" + storeOrder[i]);
                    if (values[i] == null) {
                        values[i] = "";
                    }
                }
                pv.setValues(values);
                propList.add(pv);
            }
        } catch (Exception e) {
            logger.error(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e2) {
                }
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (Exception e2) {
                }
            }
        }
        page.setRows(propList);
        return page;
    }

    @SuppressWarnings({ "rawtypes", "unchecked", "deprecation" })
    @Override
    public Page getReducePropValuesByPage(Long resId, Page page, int[] storeOrder, String startDate, String endDate, String reduceType) {
        List<PropertyValue> propList = new ArrayList<PropertyValue>();
        StringBuffer sql = new StringBuffer("select resid,time");
        StringBuilder sb = new StringBuilder();
        for (int store : storeOrder) {
            sb.append(" ,to_char(avg" + store + ",'fm999999990.00') avg" + store + ", to_char(cnt" + store + ",'fm999999990.00') cnt" + store + ", to_char(max" + store + ",'fm999999990.00') max" + store + ", to_char(min" + store + ",'fm999999990.00') min" + store);
        }
        sql.append(sb.toString());
        if ("1".equals(reduceType)) {
            sql.append(" from " + HOURREDUCETABLE + " p where 1=1 ");
        }
        if ("2".equals(reduceType)) {
            sql.append(" from " + DAYREDUCETABLE + " p where 1=1 ");
        }

        if (resId >= 0) {
            sql.append(" and p.resid =" + resId + " ");
        }
        for (int i = 0; i < storeOrder.length; i++) {
            if (storeOrder.length == 1) {
                sql.append(" and  p.avg" + storeOrder[i] + " is not null");
            } else {
                if (i == 0) {
                    sql.append(" and ( p.avg" + storeOrder[i] + " is not null");
                } else if (i == (storeOrder.length - 1)) {
                    sql.append(" or p.avg" + storeOrder[i] + " is not null )");
                } else {
                    sql.append(" or p.avg" + storeOrder[i] + " is not null");
                }
            }
        }
        if (startDate != null && startDate.length() > 0) {
            sql.append(" and p.time >= to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        if (endDate != null && endDate.length() > 0) {
            sql.append(" and p.time <= to_date('" + endDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        sql.append(" order by p.time desc ");

        // 分页
        String querySql = pageOrderSql(sql.toString(), page.getPageNo(), page.getPageSize());

        // 总条数
        page.setTotal(getTotalBySql(sql.toString()));

        logger.info(sql);

        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            pStmt = getSession().connection().prepareStatement(querySql);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                PropertyValue pv = new PropertyValue();
                pv.setResId(resId);
                pv.setTime(rs.getTimestamp("time"));
                String[] values = new String[storeOrder.length * 4];
                for (int i = 0; i < storeOrder.length; i++) {
                    if (storeOrder[i] <= 0) {
                        values[i] = "";
                        continue;
                    }
                    values[4 * i] = rs.getString("avg" + storeOrder[i]);
                    values[4 * i + 1] = rs.getString("cnt" + storeOrder[i]);
                    values[4 * i + 2] = rs.getString("max" + storeOrder[i]);
                    values[4 * i + 3] = rs.getString("min" + storeOrder[i]);
                }
                pv.setValues(values);
                propList.add(pv);
            }
        } catch (Exception e) {
            logger.error(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e2) {
                }
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (Exception e2) {
                }
            }
        }

        page.setRows(propList);
        return page;
    }

    /**
     * 获取总条数
     * 
     * @param sql
     * @return
     */
    private int getTotalBySql(String sql) {
        StringBuilder totalSql = new StringBuilder("select count(1) from (");
        totalSql.append(sql);
        totalSql.append(")");
        logger.info(totalSql.toString());
        int total = Integer.parseInt(getSession().createSQLQuery(totalSql.toString()).uniqueResult().toString());
        return total;
    }

    /**
     * 分页
     * 
     * @param sql
     * @param pageNo
     * @param pageSize
     * @return
     */
    private String pageOrderSql(String sql, int pageNo, int pageSize) {
        int startRecordIndex = getStartIndexByPage(pageNo, pageSize);
        int endRecordIndex = getEndIndexByPage(pageNo, pageSize);
        StringBuffer sb = new StringBuffer();
        sb.append("select * from (select rownum order_no,ordertb.* from (");
        sb.append(sql);
        sb.append(" )ordertb where rownum <=");
        sb.append(endRecordIndex);
        sb.append(") where order_no>=");
        sb.append(startRecordIndex);
        return sb.toString();
    }

    private int getStartIndexByPage(int pageNo, int pageSize) {
        if (pageNo <= 0 || pageSize <= 0) {
            return 0;
        }
        return (pageNo - 1) * pageSize + 1;
    }

    private int getEndIndexByPage(int pageNo, int pageSize) {
        if (pageNo <= 0 || pageSize <= 0) {
            return 0;
        }
        return pageNo * pageSize;
    }

    @SuppressWarnings({ "deprecation", "unchecked", "rawtypes" })
    @Override
    public Page getLogContentPropsByPage(Long resId, Page page, int[] storeOrder, String startDate, String endDate) {
        // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        List<String[]> list = new ArrayList<String[]>();

        StringBuffer sql = new StringBuffer("select p.resid,p.time ");
        StringBuilder sb = new StringBuilder();
        for (int store : storeOrder) {
            sb.append(", p.prop" + store);
        }
        sql.append(sb.toString());
        sql.append(", o.file_content");
        sql.append(" from " + PROPERTYVALUE + " p ," + PROPERTYOBJECTVALUE + " o where 1=1 ");
        if (resId >= 0) {
            sql.append(" and p.resid =" + resId + " and p.resid=o.res_id ");
        }
        for (int i = 0; i < storeOrder.length; i++) {
            if (storeOrder[i] > 0) {
                if (storeOrder.length == 1) {
                    sql.append(" and  p.PROP" + storeOrder[i] + " is not null");
                } else {
                    if (i == 0) {
                        sql.append(" and ( p.PROP" + storeOrder[i] + " is not null");
                    } else if (i == (storeOrder.length - 1)) {
                        sql.append(" or p.PROP" + storeOrder[i] + " is not null )");
                    } else {
                        sql.append(" or p.PROP" + storeOrder[i] + " is not null");
                    }
                }
            }
        }
        sql.append(" and o.file_content is not null ");
        if (startDate != null && startDate.length() > 0) {
            sql.append(" and p.time >= to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        if (endDate != null && endDate.length() > 0) {
            sql.append(" and p.time <= to_date('" + endDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        sql.append(" order by p.time desc ");

        String querySql = pageOrderSql(sql.toString(), page.getPageNo(), page.getPageSize());

        page.setTotal(getTotalBySql(sql.toString()));

        // td test
        // System.out.println(querySql);
        // logger.info(querySql);

        // 总条数

        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            pStmt = getSession().connection().prepareStatement(querySql);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                String[] values = new String[storeOrder.length + 1];
                for (int i = 0; i < storeOrder.length; i++) {
                    values[i] = rs.getString("PROP" + storeOrder[i]);
                    if (values[i] == null) {
                        values[i] = "";
                    }
                }
                values[values.length - 1] = rs.getString("file_content");
                list.add(values);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e2) {
                    logger.error(e2);
                }
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (Exception e2) {
                    logger.error(e2);
                }
            }
        }
        page.setRows(list);
        return page;
    }

    @SuppressWarnings("deprecation")
    @Override
    public String doPropCheckProc() {
        CallableStatement call = null;
        boolean b = false;
        try {
            call = getSession().connection().prepareCall("{call proc_property_check()}");
            call.execute();
            return "1";
        } catch (Exception e) {
            logger.error(e);
            return "0";
        } finally {
            if (call != null) {
                try {
                    call.close();
                } catch (Exception e2) {
                    logger.error(e2);
                }
            }
        }
    }

    @SuppressWarnings("deprecation")
    @Override
    public String queryCheckedTime() {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        String stacTime = "";
        try {
            pStmt = getSession().connection().prepareStatement("select distinct(stac_time) time from property_value_check");
            rs = pStmt.executeQuery();
            while (rs.next()) {
                if (stacTime.length() == 0) {
                    stacTime = rs.getString("time");
                }
            }
        } catch (Exception e) {
            logger.error(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e2) {

                }
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (Exception e2) {
                }
            }
        }
        return stacTime;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Page<String[]> queryCheckedProp(Page<String[]> page, Condition condition) {
        StringBuilder sb = new StringBuilder("select to_char(cp.stac_time,'yyyy-MM-dd hh24:mi:ss'),");
        sb.append(" cp.resid,");
        sb.append(" r.name ,");
        sb.append(" r.ip ,");
        sb.append(" cp.propid,");
        sb.append(" p.name pn,");
        sb.append(" cp.task_id,");
        sb.append(" cp.s_param,");
        sb.append(" to_char(cp.last_c_time,'yyyy-MM-dd hh24:mi:ss'),");
        sb.append(" cp.last_c_val");
        sb.append(" from property_value_check cp,res r,property p");
        sb.append(" where 1=1 and cp.resid = r.id and cp.propid = p.id ");
        sb.append(buildConForCheckedQuery(condition));
        sb.append(" order by cp.stac_time desc");

        logger.info(sb.toString());

        page.setTotal(getTotalBySql(sb.toString()));
        Query query = getSession().createSQLQuery(sb.toString()).setFirstResult(page.getFirstResultNo()).setMaxResults(page.getPageSize());
        List<Object[]> list = query.list();
        List<String[]> retList = new ArrayList<String[]>();
        for (Object[] obj : list) {
            String[] stry = new String[10];
            stry[0] = String.valueOf(obj[0]);
            stry[1] = String.valueOf(obj[1]);
            stry[2] = String.valueOf(obj[2]);
            stry[3] = String.valueOf(obj[3]);
            stry[4] = String.valueOf(obj[4]);
            stry[5] = String.valueOf(obj[5]);
            stry[6] = String.valueOf(obj[6]);
            stry[7] = String.valueOf(obj[7]);
            stry[8] = String.valueOf(obj[8]);
            stry[9] = String.valueOf(obj[9]);

            retList.add(stry);
        }
        page.setRows(retList);
        return page;
    }

    /**
     * 构造查询条件 查询 指标是否及时采集 时用到
     * 
     * @param condition
     * @return
     */
    private StringBuilder buildConForCheckedQuery(Condition condition) {
        StringBuilder sb = new StringBuilder();
        Map<String, String> condMap = condition.getWhere();

        String resid = condMap.get("resid");
        if (resid != null && resid.length() > 0) {
            sb.append(" and cp.resid =" + resid);
        }

        String ip = condMap.get("ip");
        if (ip != null && ip.length() > 0) {
            sb.append(" and r.ip like '%" + ip + "%'");
        }

        String propids = condMap.get("propids");
        if (propids != null && propids.length() > 0) {
            sb.append(" and cp.propid like '" + propids + "%'");
        }

        String status = condMap.get("status");
        if (status != null && status.length() > 0) {
            sb.append(" and cp.status in (" + status + ") ");
        }
        return sb;
    }

    /**
     * 
     * @param resIds
     * @param storeOrder
     * @param startDate 必须指定
     * @param endDate 必须指定
     * @param reduceType
     * @return
     */
    public Map<Long, List<PropertyValue>> getPropValValueByDate(Long[] resIds, int storeOrder, String startDate, String endDate, String reduceType) {
        StringBuffer sql = new StringBuffer("select resid,time");
        String valname = ("0".equals(reduceType) ? "prop" : "avg") + storeOrder;
        sql.append("," + valname);
        if ("0".equals(reduceType)) {
            sql.append(" from " + PROPERTYVALUE + " p where 1=1 ");
        } else if ("1".equals(reduceType)) {
            sql.append(" from " + HOURREDUCETABLE + " p where 1=1 ");
        } else if ("2".equals(reduceType)) {
            sql.append(" from " + DAYREDUCETABLE + " p where 1=1 ");
        }

        if (startDate != null) {
            sql.append(" and  p.time >= to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss') ");
        }
        if (endDate != null) {
            sql.append(" and  p.time <= to_date('" + endDate + "','yyyy-MM-dd hh24:mi:ss') ");
        }
        if ("0".equals(reduceType)) {
            sql.append(" and  p.PROP" + storeOrder + " is not null ");
        } else {
            sql.append(" and  p.avg" + storeOrder + " is not null ");
        }

        String ids = "";
        for (int i = 0; i < resIds.length; i++) {
            if (i == 0) {
                ids = resIds[i].toString();
            } else {
                ids += "," + resIds[i].toString();
            }
        }
        sql.append(" and p.resid in(" + ids + ") ");
        sql.append(" order by p.time asc ");
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Map<Long, List<PropertyValue>> results = new LinkedHashMap<Long, List<PropertyValue>>();
        try {
            pStmt = getSession().connection().prepareStatement(sql.toString());
            rs = pStmt.executeQuery();
            while (rs.next()) {
                long key = rs.getTimestamp("time").getTime();
                List<PropertyValue> list = results.get(key);
                if (list == null) {
                    list = new ArrayList<PropertyValue>();
                    results.put(key, list);
                }
                PropertyValue pv = new PropertyValue();
                pv.setResId(rs.getLong("resid"));
                pv.setTime(rs.getTimestamp("time"));
                try {
                    double val = rs.getDouble(valname);
                    pv.setValues(new String[] { String.valueOf(val) });
                } catch (Exception ex) {
                }
                list.add(pv);
            }

        } catch (Exception e) {
            logger.error(sql.toString(), e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e2) {
                }
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (Exception e2) {
                }
            }
        }
        return results;

    }

    public List<PropertyValue> getPropValValueByDate(Long resId, int[] storeOrder, String startDate, String endDate, String reduceType) {
        List<PropertyValue> propList = new ArrayList<PropertyValue>();
        StringBuffer sql = new StringBuffer("select resid,time");
        StringBuilder sb = new StringBuilder();
        for (int store : storeOrder) {
            sb.append(" ,var" + store);
        }

        sql.append(sb.toString());
        if ("1".equals(reduceType)) {
            sql.append(" from " + DAYREDUCEVALTABLE + " p where 1=1 ");
        }
        if ("2".equals(reduceType)) {
            sql.append(" from " + MONREDUCEVALTABLE + " p where 1=1 ");
        }
        sql.append("and ((");
        if (startDate != null && startDate.length() > 0) {
            sql.append("  p.time = to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        sql.append(" )or (");
        // 同比 月同比 求当前时间去年
        if (startDate != null && startDate.length() > 0) {
            sql.append("  p.time = add_months(to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss'),-12) or ");
            sql.append("  p.time = add_months(to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss'),-1)");
        }
        sql.append("))");

        if (resId >= 0) {
            sql.append(" and p.resid =" + resId + " ");
        }
        for (int i = 0; i < storeOrder.length; i++) {
            if (storeOrder.length == 1) {
                sql.append(" and  p." + "var" + storeOrder[i] + " is not null");
            } else {
                if (i == 0) {
                    sql.append(" and ( p." + "var" + storeOrder[i] + " is not null");
                } else if (i == (storeOrder.length - 1)) {
                    sql.append(" or p." + "var" + storeOrder[i] + " is not null )");
                } else {
                    sql.append(" or p." + "var" + storeOrder[i] + " is not null");
                }
            }
        }
        sql.append(" order by p.time asc ");
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            pStmt = getSession().connection().prepareStatement(sql.toString());
            rs = pStmt.executeQuery();

            while (rs.next()) {
                PropertyValue pv = new PropertyValue();
                pv.setResId(resId);
                pv.setTime(rs.getTimestamp("time"));
                String[] values = new String[storeOrder.length];
                for (int i = 0; i < storeOrder.length; i++) {
                    if (storeOrder[i] <= 0) {
                        values[i] = "";
                        continue;
                    }
                    values[i] = rs.getString("var" + storeOrder[i]);
                }
                pv.setValues(values);
                propList.add(pv);
            }

        } catch (Exception e) {
            logger.error(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e2) {
                }
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (Exception e2) {
                }
            }
        }
        return propList;

    }

    @Override
    public List<PropertyValue> getThendPropCompareByDate(Long resId, int[] storeOrder, String startDate, String endDate, String trendType, String reduceType, String choiceType) {
        // TODO Auto-generated method stub
        List<PropertyValue> propList = new ArrayList<PropertyValue>();
        StringBuffer sql = new StringBuffer("select resid,time");
        StringBuilder sb = new StringBuilder();
        String type = "";
        if ("0".equals(trendType)) {
            type = "avg";
        } else if ("1".equals(trendType)) {
            type = "max";
        } else {
            type = "min";
        }

        for (int store : storeOrder) {
            sb.append(" ," + type + store);
        }

        sql.append(sb.toString());
        if ("1".equals(reduceType)) {
            sql.append(" from " + HOURREDUCETABLE + " p where 1=1 ");
        }
        if ("2".equals(reduceType)) {
            sql.append(" from " + DAYREDUCETABLE + " p where 1=1 ");
        }
        sql.append("and ((");
        if (startDate != null && startDate.length() > 0) {
            sql.append("  p.time >= to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        if (endDate != null && endDate.length() > 0) {
            sql.append(" and p.time <= to_date('" + endDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        sql.append(" )or (");
        // 同比 月同比 求当前时间去年
        if (choiceType.equals("1")) {
            if (startDate != null && startDate.length() > 0) {
                sql.append("  p.time >= add_months(to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss'),-12)");
            }
            if (endDate != null && endDate.length() > 0) {
                sql.append(" and p.time <= add_months(to_date('" + endDate + "','yyyy-MM-dd hh24:mi:ss'),-12)");
            }
        }
        // 环比 月环比 求当前时间的上个月
        if (choiceType.equals("2")) {
            if (startDate != null && startDate.length() > 0) {
                sql.append("  p.time >= add_months(to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss'),-1)");
            }
            if (endDate != null && endDate.length() > 0) {
                sql.append(" and p.time <= add_months(to_date('" + endDate + "','yyyy-MM-dd hh24:mi:ss'),-1)");
            }
        }
        sql.append("))");

        if (resId >= 0) {
            sql.append(" and p.resid =" + resId + " ");
        }
        for (int i = 0; i < storeOrder.length; i++) {
            if (storeOrder.length == 1) {
                sql.append(" and  p." + type + storeOrder[i] + " is not null");
            } else {
                if (i == 0) {
                    sql.append(" and ( p." + type + storeOrder[i] + " is not null");
                } else if (i == (storeOrder.length - 1)) {
                    sql.append(" or p." + type + storeOrder[i] + " is not null )");
                } else {
                    sql.append(" or p." + type + storeOrder[i] + " is not null");
                }
            }
        }
        sql.append(" order by p.time asc ");
        // todo
        // System.out.println(sql);
        // logger.info(sql);
        // Query query = getSession().createSQLQuery(sql.toString());
        logger.info(sql);
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            pStmt = getSession().connection().prepareStatement(sql.toString());
            rs = pStmt.executeQuery();

            while (rs.next()) {
                PropertyValue pv = new PropertyValue();
                pv.setResId(resId);
                pv.setTime(rs.getTimestamp("time"));
                String[] values = new String[storeOrder.length];
                for (int i = 0; i < storeOrder.length; i++) {
                    if (storeOrder[i] <= 0) {
                        values[i] = "";
                        continue;
                    }
                    values[i] = rs.getString(type + storeOrder[i]);
                }
                pv.setValues(values);
                propList.add(pv);
            }

        } catch (Exception e) {
            logger.error(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e2) {
                }
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (Exception e2) {
                }
            }
        }
        return propList;
    }

    public String queryMaxPropValueByDate(Long resId, int[] storeOrder, String startDate, String endDate) {
        StringBuffer sql = new StringBuffer("select max(p.prop51) max");
        sql.append(" from " + PROPERTYVALUE + " p where 1=1 ");
        if (startDate != null && startDate.length() > 0) {
            sql.append(" and p.time >= to_date('" + startDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        if (endDate != null && endDate.length() > 0) {
            sql.append(" and p.time <= to_date('" + endDate + "','yyyy-MM-dd hh24:mi:ss')");
        }
        if (resId >= 0) {
            sql.append(" and p.resid =" + resId + " ");
        }
        for (int i = 0; i < storeOrder.length; i++) {
            if (storeOrder[i] > 0) {
                if (storeOrder.length == 1) {
                    sql.append(" and  p.PROP" + storeOrder[i] + " is not null");
                } else {
                    if (i == 0) {
                        sql.append(" and ( p.PROP" + storeOrder[i] + " is not null");
                    } else if (i == (storeOrder.length - 1)) {
                        sql.append(" or p.PROP" + storeOrder[i] + " is not null )");
                    } else {
                        sql.append(" or p.PROP" + storeOrder[i] + " is not null");
                    }
                }
            }
        }
        sql.append(" order by p.time asc ");
        logger.info(sql);
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        String maxValue = null;
        try {
            pStmt = getSession().connection().prepareStatement(sql.toString());
            rs = pStmt.executeQuery();
            while (rs.next()) {
                if (rs.getString("max") == null || "0".equals(rs.getString("max"))) {
                    continue;
                } else {
                    maxValue = rs.getString("max");
                }

            }
        } catch (Exception e) {
            logger.error(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e2) {
                }
            }
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (Exception e2) {
                }
            }
        }
        return maxValue;
    }

}
